package com.bears.god.sup.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.bears.god.sup.model.Income;
import com.bears.god.sup.model.Vendor;
import com.bears.god.sup.util.DBConnection;

public class VendorDAOImp implements IVendorDAO {
	private static final String FIND_FIVE = "select * from vendor LIMIT ?,?";
	private static final String FIND_ALL = "select * from vendor";
	private static final String INSERT= "insert into vendor"
			+ "(vendor,vendor_address,vendor_phone,vendor_FAX,contact_person) "
			+ "values(?,?,?,?,?)";
	private static final String FINDBYID="select * from vendor where id=?";
	private static final String UPDATE= "update vendor set "
			+ "vendor=?,vendor_address=?,vendor_phone=?,vendor_FAX=?,contact_person=?"
			+ " where id=?";
	
	@Override
	public List<Vendor> findByfiveId(int num, int endnum) throws Exception {
		
		Connection conn = null;
		PreparedStatement prep = null;
		ResultSet rs = null;
		
		List<Vendor> vendors = new ArrayList<Vendor>();
		
		try {
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(FIND_FIVE);
			prep.setInt(1, num);
			prep.setInt(2, endnum);
			rs = prep.executeQuery();

			while (rs.next()) {
				Vendor vendor = new Vendor();
				
				vendor.setId(rs.getInt("id"));
				vendor.setVendor(rs.getString("vendor"));
				vendor.setVendorAddress(rs.getString("vendor_address"));
				vendor.setVendorFAX(rs.getString("vendor_FAX"));
				vendor.setVendorPhone(rs.getString("vendor_phone"));
				vendor.setContactPerson(rs.getString("contact_person"));

				vendors.add(vendor);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, prep, conn);
		}
		return vendors;
	}


	@Override
	public void save(Vendor vendor) throws Exception {
		PreparedStatement prep = null;
		Connection conn =null;
		
		try {
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(INSERT);
			prep.setString(1, vendor.getVendor());
			prep.setString(2, vendor.getVendorAddress());
			prep.setString(3, vendor.getVendorPhone());
			prep.setString(4, vendor.getVendorFAX());
			prep.setString(5, vendor.getContactPerson());
		
			prep.executeUpdate();
			ResultSet rst = prep.getGeneratedKeys();
			rst.next();
			int id = rst.getInt(1);
			vendor.setId(id);
			
		} finally {
			DBConnection.close(prep, conn);
			}
		
	}


	@Override
	public Vendor findById(int id) throws Exception {
		PreparedStatement prep = null;
		Connection conn = null;
		ResultSet rst = null;
		Vendor vendor =null;

		try {
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(FINDBYID);
			prep.setInt(1, id);
			
			rst = prep.executeQuery();
			if(rst.next())
			{
				vendor = new Vendor();
				
				vendor.setId(rst.getInt("id"));
				vendor.setVendor(rst.getString("vendor"));
				vendor.setVendorAddress(rst.getString("vendor_address"));
				vendor.setVendorPhone(rst.getString("vendor_phone"));
				vendor.setVendorFAX(rst.getString("vendor_FAX"));
				vendor.setContactPerson(rst.getString("contact_person"));
			}
			
		}
		finally
		{
			DBConnection.close(rst, prep, conn);
		}
		
		return vendor;
	}


	@Override
	public void update(Vendor vendor) throws Exception {
		PreparedStatement prep = null;
		Connection conn = null;
		
		try {
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(UPDATE);
			prep.setString(1, vendor.getVendor());
			prep.setString(2, vendor.getVendorAddress());
			prep.setString(3, vendor.getVendorPhone());
			prep.setString(4, vendor.getVendorFAX());
			prep.setString(5, vendor.getContactPerson());
			
			prep.setInt(6, vendor.getId());
			
			prep.executeUpdate();
			
		} finally {
			DBConnection.close(prep, conn);
		}
		
	}
	
	@Override
	public List<Vendor> findAll() throws Exception{
		PreparedStatement prep = null;
		Connection conn = null;
		ResultSet rst = null;
		List<Vendor> vendor = new ArrayList<Vendor>();
		try{
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(FIND_ALL);
			rst = prep.executeQuery();
			while(rst.next()){
				Vendor v = new Vendor();
				
				v.setContactPerson(rst.getString("contact_person"));
				v.setVendor(rst.getString("vendor"));
				v.setId(rst.getInt("id"));
				v.setVendorAddress(rst.getString("vendor_address"));
				v.setVendorFAX(rst.getString("vendor_FAX"));
				v.setVendorPhone(rst.getString("vendor_phone"));
				vendor.add(v);
			}
		}finally{
			DBConnection.close(prep, conn);
		}
		return vendor;
	}

}
